//
//  LLStatusDAL.swift
//  WeiBoShiBing
//
//  Created by Bingo on 15/11/13.
//  Copyright © 2015年 Bingo. All rights reserved.
//

import UIKit

class LLStatusDAL:NSObject{


    static let  sharedInstance = LLStatusDAL()
    
    
    
    
        //查看本地是否有缓存
        
        //如果有返回数据，直接返回
        
        //如果没有缓存数据，去网络加载数据
        
        //缓存网络加载的数据到本地数据库
        
        //返回数据给调用者
    
    private let clearDate: NSTimeInterval = 60 // 7 * 24 * 60 * 60
    
//    // 清除过期的缓存数据
//    func clearCacheData() {
//        // 计算过期时间
//        let overDate = NSDate(timeIntervalSinceNow: -clearDate)
//        print("当前时间:\(NSDate())")
//        print("过期时间:\(overDate)")
//        
//        // 日期格式化成sqlite数据库的日期格式
//        let df = NSDateFormatter()
//        
//        // 设置格式     2015-11-13 15:56:02
//        df.dateFormat = "yyyy-MM-dd HH:mm:ss"
//        
//        // 将过期时间转成sqlite时间字符串
//        let overString = df.stringFromDate(overDate)
//        
//        // 生成sql语句
//        let sql = "DELETE FROM T_Status WHERE createTime < '\(overString)'"
//        
//        print("清除数据sql:\(sql)")
//        // 执行sql语句
//        LLSQliteManager.sharedManager.dbQuere.inDatabase { (db) -> Void in
//            if db.executeUpdate(sql) {
//                print("清除缓存数据成功")
//            }
            //            guard let result = db.executeQuery(sql) else {
            //                print("查询失败")
            //                return
            //            }
            //
            //            while result.next() {
            //                let createTime = result.stringForColumn("createTime")
            //                print("createTime:\(createTime)")
            //            }
//        }
//    }
//    

//        private let cleaeDate: NSTimeInterval = 7 * 24 * 60 * 60
    private let cleaeDate: NSTimeInterval = 60
    
    func clearCacheData(){
        
        let overDate = NSDate(timeIntervalSinceNow: -cleaeDate)
        //        print("当前时间:\(NSDate()）")
        print("当前时间:\(NSDate())")
        print("过期时间：\(overDate)")
        
        let df = NSDateFormatter()
        // 设置格式     2015-11-13 15:56:02
        df.dateFormat = "yyyy-MM-dd HH:mm:ss"
        
        let dateString = df.stringFromDate(overDate)
        
        let sql = "DELETE FROM T_Status WHERE createTime < '\(dateString)'"
        
        print("\(sql)")
        
        LLSQliteManager.sharedManager.dbQuere.inDatabase { (db) -> Void in
            if db.executeUpdate(sql){
                print("清除缓存成功")
            }
        }
    }
    
     func loadStatus(since_id: Int, max_id: Int,finish:(array:[[String:AnyObject]]?,error:NSError?) ->()) {
        
        loadCacheStatus(since_id, max_id: max_id) { (array) -> () in
            
            if array != nil && array?.count > 0{
                print("加载缓存数据：\(array?.count)")
                
                finish(array: array, error:nil)
                
                return
            }
            
            
            LLNetworkTools.sharedInstance.loadStatus(since_id, max_id: max_id, finished: { (result, error) -> () in
                if error != nil{
                    print("加载数据失败")
                    finish(array: nil, error: error)
                    return
                }
                
                if let array = result?["statuses"] as? [[String: AnyObject]]{
                
                    self.saveStatus(array)
                    
                    finish(array: array, error: nil)
                }
            })
            
            
        }
        
    }
   private  func loadCacheStatus(since_id: Int, max_id: Int,finish:(array:[[String:AnyObject]]?) ->()) {
        
        let userId = LLUserAccount.loadAccount()!.uid!
        
        var sql = "SELECT statusId,status,userId FROM T_Status \n" +
        "WHERE userId =\(userId) \n"
        
        if since_id > 0{
            sql += "AND statusId > \(since_id) \n"
            
        }
        
        if max_id > 0 {
        
            sql += "AND statusId < \(max_id) \n"
        }
        
        sql += "ORDER BY statusId DESC LIMIT 20"
        
        print("加载缓存数据sql:\n\t\(sql)")
        
        LLSQliteManager.sharedManager.dbQuere.inDatabase { (db) -> Void in
            
            guard let result = db.executeQuery(sql) else{
            
                print("加载缓存数据失败")
                
                finish(array: nil)
                return
            }
            
            var statuses = [[String:AnyObject]]()
            
            while result.next(){
            
                let statusString = result.stringForColumn("status")
                
                if let statusData = statusString.dataUsingEncoding(NSUTF8StringEncoding){
                    
                    
                    let json = try! NSJSONSerialization.JSONObjectWithData(statusData, options: NSJSONReadingOptions(rawValue: 0))
                    
                    let dict = json as! [String:AnyObject]
                    
                    statuses.append(dict)
                    
                }
            }
            
            finish(array: statuses)
            
        }
        
        
        
    }
   
   private func saveStatus(statuses:[[String:AnyObject]]){
        
        let userId = Int(LLUserAccount.loadAccount()!.uid!)!
        
        let sql = "INSERT OR REPLACE INTO T_Status(statusId,status,userId) VALUES (?,?,?);"
        
        LLSQliteManager.sharedManager.dbQuere.inTransaction { (db, rollback) -> Void in
           
            for dict in statuses{
                
                let statusId = dict["id"] as! Int
                
                let data = try! NSJSONSerialization.dataWithJSONObject(dict, options: NSJSONWritingOptions(rawValue: 0))
                
                let StatusString = String(data: data, encoding: NSUTF8StringEncoding)!
                
                if db.executeUpdate(sql, statusId,StatusString,userId){
                
                        print("数据加载成功")
                }else{
                
                    print("数据微博加载失败")
                    rollback.memory = true
                    break
                }
                
            }
        }
        
        print("缓存数据，添加：\(statuses.count)条数据")
    }
}

